package com.zoroki.student_score_system.mapper;

import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.zoroki.student_score_system.domain.Score;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.zoroki.student_score_system.vo.ScoreVo;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

/**
* @author Administrator
* @description 针对表【score】的数据库操作Mapper
* @createDate 2025-06-30 12:49:22
* @Entity com.zoroki.student_score_system.domain.Score
*/
@Mapper
public interface ScoreMapper extends BaseMapper<Score> {
    @Select("select `user`.`id` AS `id`,`user`.`username` AS `username`,`user`.`real_name` AS `real_name`,`user`.`gender` AS `gender`,`c`.`id` AS `class_id`,`c`.`name` AS `class_name`,`course`.`id` AS `course_id`,`course`.`name` AS `course_name`,`u_teacher`.`id` AS `teacher_id`,`u_teacher`.`real_name` AS `teacher_name`,`course`.`credit` AS `credit`,`score`.`id` AS `score_id`,`score`.`score` AS `score`,`course`.`full_mark` AS `full_mark`,`score`.`exam_date` AS `exam_date`,`semester`.`name` AS `semester_name`,`semester`.`start_date` AS `semester_start_date`,`semester`.`end_date` AS `semester_end_date` from ((((((`user` left join `score` on((`user`.`id` = `score`.`student_id`))) left join `user_class` `uc` on((`user`.`id` = `uc`.`user_id`))) left join `class` `c` on((`uc`.`class_id` = `c`.`id`))) left join `course` on((`score`.`course_id` = `course`.`id`))) left join `semester` on((`semester`.`id` = `course`.`semester_id`))) left join `user` `u_teacher` on(((`course`.`teacher_id` = `u_teacher`.`id`) and (`u_teacher`.`role` = 'TEACHER')))) " +
            "where ((`user`.`role` = 'STUDENT') and (`course`.`id` is not null)) " +
            "order by `c`.`name`,`course`.name,`score`.`score` desc")
    Page<ScoreVo> getAllScore(Page<ScoreVo> page);

    @Select("select `user`.`id` AS `id`,`user`.`username` AS `username`,`user`.`real_name` AS `real_name`,`user`.`gender` AS `gender`,`c`.`id` AS `class_id`,`c`.`name` AS `class_name`,`course`.`id` AS `course_id`,`course`.`name` AS `course_name`,`u_teacher`.`id` AS `teacher_id`,`u_teacher`.`real_name` AS `teacher_name`,`course`.`credit` AS `credit`,`score`.`id` AS `score_id`,`score`.`score` AS `score`,`course`.`full_mark` AS `full_mark`,`score`.`exam_date` AS `exam_date`,`semester`.`name` AS `semester_name`,`semester`.`start_date` AS `semester_start_date`,`semester`.`end_date` AS `semester_end_date` from ((((((`user` left join `score` on((`user`.`id` = `score`.`student_id`))) left join `user_class` `uc` on((`user`.`id` = `uc`.`user_id`))) left join `class` `c` on((`uc`.`class_id` = `c`.`id`))) left join `course` on((`score`.`course_id` = `course`.`id`))) left join `semester` on((`semester`.`id` = `course`.`semester_id`))) left join `user` `u_teacher` on(((`course`.`teacher_id` = `u_teacher`.`id`) and (`u_teacher`.`role` = 'TEACHER')))) " +
            "where ((`user`.`role` = 'STUDENT') and (`course`.`id` is not null) and (`user`.`username` like CONCAT('%',#{username},'%')))" +
            "order by `c`.`name`,`course`.name,`score`.`score` desc")
    Page<ScoreVo> getScoreByUsername(Page<ScoreVo> page, String username);

    @Select("select `user`.`id` AS `id`,`user`.`username` AS `username`,`user`.`real_name` AS `real_name`,`user`.`gender` AS `gender`,`c`.`id` AS `class_id`,`c`.`name` AS `class_name`,`course`.`id` AS `course_id`,`course`.`name` AS `course_name`,`u_teacher`.`id` AS `teacher_id`,`u_teacher`.`real_name` AS `teacher_name`,`course`.`credit` AS `credit`,`score`.`id` AS `score_id`,`score`.`score` AS `score`,`course`.`full_mark` AS `full_mark`,`score`.`exam_date` AS `exam_date`,`semester`.`name` AS `semester_name`,`semester`.`start_date` AS `semester_start_date`,`semester`.`end_date` AS `semester_end_date` from ((((((`user` left join `score` on((`user`.`id` = `score`.`student_id`))) left join `user_class` `uc` on((`user`.`id` = `uc`.`user_id`))) left join `class` `c` on((`uc`.`class_id` = `c`.`id`))) left join `course` on((`score`.`course_id` = `course`.`id`))) left join `semester` on((`semester`.`id` = `course`.`semester_id`))) left join `user` `u_teacher` on(((`course`.`teacher_id` = `u_teacher`.`id`) and (`u_teacher`.`role` = 'TEACHER')))) " +
            "where ((`user`.`role` = 'STUDENT') and (`course`.`id` is not null) and (`user`.`real_name` like CONCAT('%',#{realName},'%')))" +
            "order by `c`.`name`,`course`.name,`score`.`score` desc")
    Page<ScoreVo> getScoreByRealName(Page<ScoreVo> page, String realName);

    @Select("select `user`.`id` AS `id`,`user`.`username` AS `username`,`user`.`real_name` AS `real_name`,`user`.`gender` AS `gender`,`c`.`id` AS `class_id`,`c`.`name` AS `class_name`,`course`.`id` AS `course_id`,`course`.`name` AS `course_name`,`u_teacher`.`id` AS `teacher_id`,`u_teacher`.`real_name` AS `teacher_name`,`course`.`credit` AS `credit`,`score`.`id` AS `score_id`,`score`.`score` AS `score`,`course`.`full_mark` AS `full_mark`,`score`.`exam_date` AS `exam_date`,`semester`.`name` AS `semester_name`,`semester`.`start_date` AS `semester_start_date`,`semester`.`end_date` AS `semester_end_date` from ((((((`user` left join `score` on((`user`.`id` = `score`.`student_id`))) left join `user_class` `uc` on((`user`.`id` = `uc`.`user_id`))) left join `class` `c` on((`uc`.`class_id` = `c`.`id`))) left join `course` on((`score`.`course_id` = `course`.`id`))) left join `semester` on((`semester`.`id` = `course`.`semester_id`))) left join `user` `u_teacher` on(((`course`.`teacher_id` = `u_teacher`.`id`) and (`u_teacher`.`role` = 'TEACHER')))) " +
            "where ((`user`.`role` = 'STUDENT') and (`course`.`id` is not null) and (`c`.`name` like CONCAT('%',#{ClassName},'%')))" +
            "order by `c`.`name`,`course`.name,`score`.`score` desc")
    Page<ScoreVo> getScoreByClassName(Page<ScoreVo> page, String ClassName);

    @Select("select `user`.`id` AS `id`,`user`.`username` AS `username`,`user`.`real_name` AS `real_name`,`user`.`gender` AS `gender`,`c`.`id` AS `class_id`,`c`.`name` AS `class_name`,`course`.`id` AS `course_id`,`course`.`name` AS `course_name`,`u_teacher`.`id` AS `teacher_id`,`u_teacher`.`real_name` AS `teacher_name`,`course`.`credit` AS `credit`,`score`.`id` AS `score_id`,`score`.`score` AS `score`,`course`.`full_mark` AS `full_mark`,`score`.`exam_date` AS `exam_date`,`semester`.`name` AS `semester_name`,`semester`.`start_date` AS `semester_start_date`,`semester`.`end_date` AS `semester_end_date` from ((((((`user` left join `score` on((`user`.`id` = `score`.`student_id`))) left join `user_class` `uc` on((`user`.`id` = `uc`.`user_id`))) left join `class` `c` on((`uc`.`class_id` = `c`.`id`))) left join `course` on((`score`.`course_id` = `course`.`id`))) left join `semester` on((`semester`.`id` = `course`.`semester_id`))) left join `user` `u_teacher` on(((`course`.`teacher_id` = `u_teacher`.`id`) and (`u_teacher`.`role` = 'TEACHER')))) " +
           "where ((`user`.`role` = 'STUDENT') and (`course`.`id` is not null) and (`course`.`name` like CONCAT('%',#{CourseName},'%')))" +
            "order by `c`.`name`,`course`.name,`score`.`score` desc")
    Page<ScoreVo> getScoreByCourseName(Page<ScoreVo> page, String CourseName);

    @Select("select `user`.`id` AS `id`,`user`.`username` AS `username`,`user`.`real_name` AS `real_name`,`user`.`gender` AS `gender`,`c`.`id` AS `class_id`,`c`.`name` AS `class_name`,`course`.`id` AS `course_id`,`course`.`name` AS `course_name`,`u_teacher`.`id` AS `teacher_id`,`u_teacher`.`real_name` AS `teacher_name`,`course`.`credit` AS `credit`,`score`.`id` AS `score_id`,`score`.`score` AS `score`,`course`.`full_mark` AS `full_mark`,`score`.`exam_date` AS `exam_date`,`semester`.`name` AS `semester_name`,`semester`.`start_date` AS `semester_start_date`,`semester`.`end_date` AS `semester_end_date` from ((((((`user` left join `score` on((`user`.`id` = `score`.`student_id`))) left join `user_class` `uc` on((`user`.`id` = `uc`.`user_id`))) left join `class` `c` on((`uc`.`class_id` = `c`.`id`))) left join `course` on((`score`.`course_id` = `course`.`id`))) left join `semester` on((`semester`.`id` = `course`.`semester_id`))) left join `user` `u_teacher` on(((`course`.`teacher_id` = `u_teacher`.`id`) and (`u_teacher`.`role` = 'TEACHER')))) " +
            "where ((`user`.`role` = 'STUDENT') and (`course`.`id` is not null) and (`u_teacher`.`real_name` like CONCAT('%',#{TeacherName},'%')))" +
            "order by `c`.`name`,`course`.name,`score`.`score` desc")
    Page<ScoreVo> getScoreByTeacherName(Page<ScoreVo> page, String TeacherName);
}




